package com.mtl.inmigrant.shoes.sqlite;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.mtl.inmigrant.shoes.entity.ShoeActionsInmigrant;
import com.mtl.inmigrant.shoes.entity.TypeActivityShoes.typeActionShoe;


/**
 * DAO class - DB connection, add new showAction
 * @author MaryG
 *
 */
public class ShoesDataSource {
	
	
	private SQLiteDatabase database;
	private MyConnectionSQLiteHelper shoeDBHelper;
	Cursor cursor;

	
	private String[] allColumnsTableActivity = { MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY,
			MyConnectionSQLiteHelper.COLUMN_TABLE1_STATUS, 
			MyConnectionSQLiteHelper.COLUMN_TABLE1_DESCRIPTION };
	
	private String[] allColumnsTableTask = { MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_ACTIVITY,
			MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_TASK, 
			MyConnectionSQLiteHelper.COLUMN_TABLE2_DATE,
			MyConnectionSQLiteHelper.COLUMN_TABLE2_STATUS,
			MyConnectionSQLiteHelper.COLUMN_TABLE2_DESCRIPTION };
	
	private String[] allColumnsTableReferential = { MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_ACTIVITY,
			MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_TASK, 
			MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_REFERENTIAL, 
			MyConnectionSQLiteHelper.COLUMN_TABLE3_STATUS,
			MyConnectionSQLiteHelper.COLUMN_TABLE3_DESCRIPTION };
	
	
	
	public ShoesDataSource(Context context) {
		shoeDBHelper = new MyConnectionSQLiteHelper(context);
	}

	
	public void open() throws SQLException {
		database = shoeDBHelper.getWritableDatabase();
	}

	public void close() {
		shoeDBHelper.close();
	}
	
	
	//----- CRUD operations for action:ACTIVITY
	public void createShoeAction(ShoeActionsInmigrant shoeActionsInmigrant, String KeyActivityP, String keyTaskP) {
		
		ContentValues values = new ContentValues();
		long insertId = -1;
		
		if (shoeActionsInmigrant.getTypeAction().equals(typeActionShoe.ACTIVITY.toString())){
			
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY, shoeActionsInmigrant.getIdActivity());
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_STATUS, new Integer(0));
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_DESCRIPTION, shoeActionsInmigrant.getNameAction());
			
			insertId = database.insert(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG, null,
					values);
			
		}else if(shoeActionsInmigrant.getTypeAction().equals(typeActionShoe.TASK.toString())){
			
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_ACTIVITY, KeyActivityP);
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_TASK, shoeActionsInmigrant.getIdActivity() );//in test!!
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_DATE, shoeActionsInmigrant.getDateShoe());
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_STATUS, new Integer(0));
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_DESCRIPTION, shoeActionsInmigrant.getNameAction());
			
			insertId = database.insert(MyConnectionSQLiteHelper.TABLE2_TASK_INMIG, null,
					values);
			
		}else if (shoeActionsInmigrant.getTypeAction().equals(typeActionShoe.REFERENTIAL.toString())){
			
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_ACTIVITY, KeyActivityP);
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_TASK, keyTaskP);//in test!!
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_REFERENTIAL, shoeActionsInmigrant.getIdActivity());
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE3_STATUS, new Integer(0));
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE3_DESCRIPTION, shoeActionsInmigrant.getNameAction());
			
			insertId = database.insert(MyConnectionSQLiteHelper.TABLE3_REFERENTIAL, null,
					values);			
			
		}
		

		if(insertId >= 0) {
			
			//no message
		
		}else{
			//error message
		}
		
		cursor.close();
	}
	
	
	
	public ShoeActionsInmigrant getOneShoeAction(String keyAction, String typeAction){
		
		ShoeActionsInmigrant shoeActionsInmigrant =  null;
		
		if(typeAction.equals(typeActionShoe.ACTIVITY.toString())){
			
			cursor = database.query(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG,
					allColumnsTableActivity, MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY + " = '" + keyAction
					+ "' ", null, null, null, null);
			
		}else if(typeAction.equals(typeActionShoe.TASK.toString())){
			
			cursor = database.query(MyConnectionSQLiteHelper.TABLE2_TASK_INMIG,
					allColumnsTableTask, MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_ACTIVITY + " = '" + keyAction
					+ "' ", null, null, null, null);//in test!!
			
		}else if(typeAction.equals(typeActionShoe.REFERENTIAL.toString()))	{
			
			cursor = database.query(MyConnectionSQLiteHelper.TABLE2_TASK_INMIG,
					allColumnsTableReferential, MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_REFERENTIAL + " = '" + keyAction
					+ "' ", null, null, null, null);//in test!!
			
		}
		
		
		if( cursor.getCount() > 0){			
			cursor.moveToFirst();	
			shoeActionsInmigrant =  new ShoeActionsInmigrant();
			shoeActionsInmigrant = cursorToShoeAction(cursor, typeActionShoe.ACTIVITY.toString());			
		}
		
		cursor.close();		
		return shoeActionsInmigrant;
		
	}
	
	
	public List<ShoeActionsInmigrant> getActionEnfants(String []keyParentAction, String typeAction){
		
		List<ShoeActionsInmigrant> shoesActions = new ArrayList<ShoeActionsInmigrant>();
		ShoeActionsInmigrant shoeAction = null;
		
		String query = null;
		
		if(typeAction.equals(typeActionShoe.ACTIVITY.toString())){
			
			query = "SELECT  ID_T, DESCRIP_T, STATUS_T, DATETASK  FROM TASK_I WHERE ACTIVITY_ID = ?";		
			
		}else if(typeAction.equals(typeActionShoe.TASK.toString())){
			
			query = " SELECT  ID_R, DESCRIP_R, STATUS_R  FROM REFERENTIAL_I WHERE TASK_ID = ? AND ACTIVITY_ID = ?";		
			
		}
		
		if(query!=null){
			cursor = database.rawQuery(query, keyParentAction);
		}
		
		if (cursor != null)
			cursor.moveToFirst();

		while (!cursor.isAfterLast()) {			
			if(typeAction.equals(typeActionShoe.ACTIVITY.toString())){//enfants from activity	
				shoeAction = cursorKeyActionToShoeAction(cursor, typeActionShoe.TASK.toString());
				
			}else if (typeAction.equals(typeActionShoe.TASK.toString())){
				shoeAction = cursorKeyActionToShoeAction(cursor, typeActionShoe.REFERENTIAL.toString());
				
			}
			
			if(shoeAction != null){
			 shoesActions.add(shoeAction);
			}
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return shoesActions;
		
	}
	
	
	

	public void deleteShoeAction(ShoeActionsInmigrant shoeAction) {
		String idShoeAction = shoeAction.getIdActivity();
		System.out.println("action shoe deleted with id: " + idShoeAction);
		database.delete(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG, MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY
				+ " = '" + shoeAction + "' ", null);
		
	}
	
	public void deleteAllShoeAction() {
		System.out.println("action shoe all deleted");
		database.delete(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG, null, null);
		database.delete(MyConnectionSQLiteHelper.TABLE2_TASK_INMIG, null, null);
		database.delete(MyConnectionSQLiteHelper.TABLE3_REFERENTIAL, null, null);

	}
	
	
	public List<ShoeActionsInmigrant> getAllShoesActions() {
		
		List<ShoeActionsInmigrant> shoesActions = new ArrayList<ShoeActionsInmigrant>();
		
		cursor = database.query(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG,
										allColumnsTableActivity, null, null, null, null, null);
		cursor.moveToFirst();
		
		while (!cursor.isAfterLast()) {			
			ShoeActionsInmigrant shoeAction = cursorToShoeAction(cursor, typeActionShoe.ACTIVITY.toString());
			shoesActions.add(shoeAction);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return shoesActions;
	}
	
	
	public HashMap<String,String> getCurrentKeys(){
		
		StringBuffer MySqlOnlyKeys = new StringBuffer();
		HashMap<String,String> keysActions = new HashMap<String, String>();
		
		MySqlOnlyKeys.append(" select A.id_A as IdA, null as IdT, null as IdR  from activity_I A " +
							 " union	" +
							 " select T.activity_id as IdA, T.id_T  as IdT, null as IdR from Task_I T " +
							 " union " +
							 " select  R.activity_id as IdA, R.Task_id as IdT, R.id_R as IdR from Referential_I R ");		
		
							
		
		cursor = database.rawQuery(MySqlOnlyKeys.toString(), null);
		
		cursor.moveToFirst();
		
		while (!cursor.isAfterLast()) {			
			
			String keyActions = cursorToStringKeys(cursor);
			keysActions.put(keyActions, "keyActions");
			cursor.moveToNext();
		}
		cursor.close();
		return keysActions;
		
	}
	
	
	
	public void updateDescriptionActivity(String[] keys, String newDescriptionA ){
		
		ContentValues cv = new ContentValues();
		
		cv.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_DESCRIPTION, newDescriptionA);			
		
		database.update(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG, 
						cv, 
						MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY+"=?", keys);
		
		
	}
	
	
	public void updateDescriptionTask(String[] keys, String newDescriptionT ){
		
		ContentValues cv = new ContentValues();
		
		cv.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_DESCRIPTION, newDescriptionT);			
		
		database.update(MyConnectionSQLiteHelper.TABLE2_TASK_INMIG, 
						cv, 
						MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_TASK+"=?"
						+ " and " +
						MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_ACTIVITY+"=?", keys);
		
		
	}
	
	
	public void updateDescriptionReferencial(String[] keys, String newDescriptionR ){
		
		ContentValues cv = new ContentValues();
		
		cv.put(MyConnectionSQLiteHelper.COLUMN_TABLE3_DESCRIPTION, newDescriptionR);			
		
		database.update(MyConnectionSQLiteHelper.TABLE3_REFERENTIAL, 
						cv, 
						MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_REFERENTIAL+"=?"
						+ " and " +
						MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_TASK+"=?"
						+ " and " +
						MyConnectionSQLiteHelper.COLUMN_TABLE3_ID_ACTIVITY+"=?", keys);
		
		
	}
	
	//----- CRUD operations for action:TASK
	
	private ShoeActionsInmigrant cursorKeyActionToShoeAction(Cursor cursor, String typeAction){
		
		//  ID_T, DESCRIP_T, STATUS_T, DATETASK, 
		ShoeActionsInmigrant shoeAction = new ShoeActionsInmigrant();
		shoeAction.setIdActivity(cursor.getString(0));
		shoeAction.setNameAction(cursor.getString(1));
		shoeAction.setTypeAction(typeAction);
		
		if (cursor.getInt(2) == 1){
			shoeAction.setStatus(true);
		}else{
			shoeAction.setStatus(false);
		}
		
		if(typeAction.equals(typeActionShoe.TASK.toString())){
			shoeAction.setDateShoe(cursor.getString(3));
		}
		return shoeAction;
		
	}
	
	
	
	private String cursorToStringKeys(Cursor cursor) {
		
		StringBuffer allKeys = new StringBuffer();
		
		String keyA = cursor.getString(0);		
		if(keyA != null){
			allKeys.append(keyA);
		}
		
		
		String keyT = cursor.getString(1);
		if(keyT != null){
			allKeys.append("-");
			allKeys.append(keyT);
		}	
		
		String keyR = cursor.getString(2);
		if(keyR != null){
			allKeys.append("-");
			allKeys.append(keyR);
		}
 
		return allKeys.toString();
	}
	
	
private ShoeActionsInmigrant cursorToShoeAction(Cursor cursor, String typeAction) {
		
		ShoeActionsInmigrant shoeAction = new ShoeActionsInmigrant();
		shoeAction.setIdActivity(cursor.getString(0));
		if (cursor.getInt(1) == 1)
			shoeAction.setStatus(true);
		shoeAction.setNameAction(cursor.getString(2));
		shoeAction.setTypeAction(typeAction);

		return shoeAction;
	}
	
	public Long createShoeActionOscar(String actionId, Integer status) {
			
			ContentValues values = new ContentValues();
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY, actionId);
			values.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_STATUS, status);
			
			long insertId = database.insert(MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG, null, values);
			
			return new Long(insertId);
			
	}
	
	public void updateActivity(String[] keys, Integer newStatus, String typeAction){
		
		ContentValues cv = new ContentValues();
		String where=null;
		String table=null;
		
		if (typeAction.equals(typeActionShoe.ACTIVITY.toString())){
			
			table=MyConnectionSQLiteHelper.TABLE1_ACTIVITY_INMIG;
			where = MyConnectionSQLiteHelper.COLUMN_TABLE1_ID_ACTIVITY+"=?";
			cv.put(MyConnectionSQLiteHelper.COLUMN_TABLE1_STATUS, newStatus);
			
		}else if (typeAction.equals(typeActionShoe.TASK.toString())){
			
			table=MyConnectionSQLiteHelper.TABLE2_TASK_INMIG;
			where =MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_TASK+"=?"
					+ " and " +
					MyConnectionSQLiteHelper.COLUMN_TABLE2_ID_ACTIVITY+"=?";
			cv.put(MyConnectionSQLiteHelper.COLUMN_TABLE2_STATUS, newStatus);
		}
		
		database.update(table, cv, where, keys);
	}

}
